| ClickHouse基本语法 | 您所在的位置：网站首页 › create table test as › ClickHouse基本语法 |
ClickHouse基本语法
|
一、DDL基础 目前只有MergeTree系列的表引擎和Distributed表引擎支持 ALTER查询，所以在进行alter操作的时候注意表的引擎。 CREATE TABLE tb_test2 ( `id` Int8, `name` String comment '用户名' default 'VIP', --设置列的默认值 ) ENGINE = MergeTree() ORDER BY id ; --添加字段 alter table tb_test2 add column age UInt8 ; alter table tb_test2 add column gender String after name ; -- 删除字段 alter table tb_test2 drop column age ; -- 修改字段的数据类型 alter table tb_test2 modify column gender UInt8 default 0 ; --修改 / 添加字段的注释 alter table tb_test2 comment column name '用户名' ; -- 修改表名 rename table tb_test1 to t1 ; -- 修改多张表名 rename table tb_test2 to t2 , t1 to tt1 ; -- 移动表到另一数据库中 rename table t2 to test1.t ;二、分区表操作目前只有MergeTree系列的表引擎支持数据分区 create table test_partition( id String, ctime DateTime )engine=MergeTree() partition by toYYYYMM(ctime) order by (id) ;1、查看表中的分区 SELECT partition_id,name,table,partition,active FROM system.parts WHERE table = 'test_partition' AND active = 1 ;2、添加/删除分区 --删除分区：删除分区以后，分区中的所有的数据全部删除 alter table test_partition drop partition '202105' ;3、合并分区 optimize table test_partition final;4、复制分区 clickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景 复制分区需要满足两个前提条件： 两张表需要拥有相同的分区键它们的表结构完全相同。--创建表 create table test_partition1 as test_partition ; --复制一张表的分区到另一张表中 alter table test_partition1 replace partition '202106' from test_partition5、重置分区数据 如果数据表某一列的数据有误，需要将其重置为初始值，如果设置了默认值那么就是默认值数据，如果没有设置默认值，系统会给出默认的初始值 注意：不能重置主键和分区字段 alter table test_partition1 clear column name in partition '202105';6、卸载/装载分区 使用场景：分区数据的迁移和备份 ①卸载分区detach 分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。该目录已经脱离了clickhouse的管理，clickhouse并不会主动清理这些文件。 alter table test_partition detach partition '202105';②装载分区attach 将detached子目录的某个分区重新装载回去。 alter table test_partition attach partition '202105';三、视图1、普通视图 不会存储任何数据，只是一层简单的select查询映射，对查询性能不会有任何增强 create view test_view as select id,upper(name),role from tb_test;2、物化视图 支持表引擎，数据保存形式由它的表引擎决定物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新populate修饰符决定物化视图的初始化策略，如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了INTO SELECT一般。物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据会保留create materialized view mater_test_view engine=Log populate as select * from tb_test;四、查询语法1、with子句 ClickHouse支持CTE（Common Table Expression，公共表表达式），以增强查询语句的表达 --1、定义变量 with pow(2,2) as res select pow(res,2) --2、调用函数 WITH toDate(create_time) AS bday SELECT user_id,score,bday FROM test_a; --3、定义子查询 with ( select user_id,score from test_a limit 1 ) as sub select user_id,score,sub from test_a;2、from SQL是一种面向集合的编程语言 ,from决定了程序从那里读取数据 。 表中查询数据子查询中查询数据表函数中查询数据 表函数参考--1、file 数据文件必须在指定的目录下 /var/lib/clickhouse/user_files SELECT * FROM file('demo.csv', 'CSV', 'id Int8,name String , age UInt8') --2、numbers select * from numbers(2,10) ; select toDate('2021-01-01') + number as date from numbers(365); --3、mysql CH可以直接从mysql服务中查询数据 SELECT * FROM mysql('localhost:3306', 'test', 't_sku', 'root', '123456'); --4、hdfs SELECT * FROM hdfs('hdfs://hdfs1:9000/test', 'TSV', 'column1 UInt32, column2 UInt32')3、关联查询  JOIN子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。 （1）连接精度：支持ALL(默认)、ANY和ASOF三种类型。可通过join_default_strictness配置参数修改默认的连接精度类型。 all：左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据。any：左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据。asof：模糊连接，连接键之后追加定义一个模糊连接的匹配条件asof_column。（2）连接类型：分为外连接(left/right/full)、内连接(inner)和交叉连接（cross）三种 4、array join ARRAY JOIN子句允许在数据表的内部，与数组或嵌套类型的字段进行JOIN操作，从而将一行数组展开为多行。类似于hive中的explode炸裂函数的功能。 在一条select语句中，只能存在一个array join(使用子查询除外) inner array join（默认）：排除掉了空数组left aray join：在inner array join中被排除掉的空数组出现在了返回的结果集中当同时对多个数组字段进行array join操作，查询的计算逻辑按行合并而不是产生笛卡尔积drop table if exists test_arrayjoin; CREATE TABLE test_arrayjoin ( id String, hobby Array(String) )ENGINE = Memory; insert into test_arrayjoin values (1, ['eat','drink','sleep']),(2, ['study','read','sport']),(3, []); select id ,hobby ,hb from test_arrayjoin array join hobby as hb; --同时对多个数组字段进行array join操作 select id ,hobby ,hb ,arrayMap(x ->concat(x,'ABC'),hobby) as hobbyCon ,hyc ,arrayEnumerate(hobby) as hobbyEnum ,hbe from test_arrayjoin array join hobby as hb ,hobbyCon as hyc ,hobbyEnum as hbe;5、with模型 with cube：如果聚合键的个数为n，则组合的个数为2^nwith rollup：按照聚合键从右向左上卷数据，基于聚合函数生成分组小计和总计，如果聚合键的个数为n，组合的个数为n+1。with totals：基于聚合函数对所有数据进行总计。6、order by子句 在MergeTree中指定order by之后，数据在各个分区内会按照其定义的规则排序，这是一种分区内的局部排序order by子句指定全局排序，对于NULL值的排序，目前Clickhouse有null值最后和null值优先两种策略--null值最后 select arrayJoin([1,2,null,0/0,3]) as v1 order by v1 desc nulls last; --null值优先 select arrayJoin([1,2,null,0/0,3]) as v1 order by v1 desc nulls first;7、limit by子句 运行于order by之后和limit之前，能够按照指定分组，最多返回前n行数据，常用于top n的查询场景limit子句limit by子句可以同时使用select database,table,max(bytes_on_disk) as bytes from system.parts group by database, table order by database,bytes as desc limit 2 by database --limit by支持offset偏移量获取数据 select database,table,max(bytes_on_disk) as bytes from system.parts group by database, table order by database,bytes as desc limit 3 offset 1 by database --limit 1,3 by database |
【本文地址】
公司简介
联系我们
| 今日新闻 |
| 推荐新闻 |
| 专题文章 |